/**
 * 
 */
package vn.edu.fpt.xml.hrm.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashSet;
import java.util.Properties;
import java.util.Set;

import vn.edu.fpt.xml.hrm.common.AppConstant;
import vn.edu.fpt.xml.hrm.common.CheckUtil;
import vn.edu.fpt.xml.hrm.common.FormatUtil;
import vn.edu.fpt.xml.hrm.pojo.Activity;
import vn.edu.fpt.xml.hrm.pojo.Employee;
import vn.edu.fpt.xml.hrm.pojo.Project;

/**
 * @author Yoshimi
 * 
 */
public class ActivityBean {
	private String dbms;
	private String serverName;
	private String port;
	private String dbName;
	private boolean integratedSecurity;
	private String username;
	private String password;

	/**
	 * 
	 */
	public ActivityBean() {
		dbms = AppConstant.DB_DBMS;
		serverName = AppConstant.DB_SERVER_NAME;
		port = AppConstant.DB_PORT;
		dbName = AppConstant.DB_NAME;
		integratedSecurity = AppConstant.DB_INTEGRATED_SECURITY;
		username = AppConstant.DB_USERNAME;
		password = AppConstant.DB_PASSWORD;
	}

	/**
	 * @return the dbms
	 */
	protected String getDbms() {
		return dbms;
	}

	/**
	 * @param dbms the dbms to set
	 */
	protected void setDbms(String dbms) {
		this.dbms = dbms;
	}

	/**
	 * @return the serverName
	 */
	protected String getServerName() {
		return serverName;
	}

	/**
	 * @param serverName the serverName to set
	 */
	protected void setServerName(String serverName) {
		this.serverName = serverName;
	}

	/**
	 * @return the port
	 */
	protected String getPort() {
		return port;
	}

	/**
	 * @param port the port to set
	 */
	protected void setPort(String port) {
		this.port = port;
	}

	/**
	 * @return the dbName
	 */
	protected String getDbName() {
		return dbName;
	}

	/**
	 * @param dbName the dbName to set
	 */
	protected void setDbName(String dbName) {
		this.dbName = dbName;
	}

	/**
	 * @return the integratedSecurity
	 */
	protected boolean isIntegratedSecurity() {
		return integratedSecurity;
	}

	/**
	 * @param integratedSecurity the integratedSecurity to set
	 */
	protected void setIntegratedSecurity(boolean integratedSecurity) {
		this.integratedSecurity = integratedSecurity;
	}

	/**
	 * @return the username
	 */
	protected String getUsername() {
		return username;
	}

	/**
	 * @param username the username to set
	 */
	protected void setUsername(String username) {
		this.username = username;
	}

	/**
	 * @return the password
	 */
	protected String getPassword() {
		return password;
	}

	/**
	 * @param password the password to set
	 */
	protected void setPassword(String password) {
		this.password = password;
	}
	
	private Connection connect() throws ClassNotFoundException, SQLException {
		Connection connection = null;
		String url = null;
		Properties properties = null;
		// 1. setup connection
		// database url
		url = "jdbc:" + dbms + "://" + serverName + ":" + port;
		// connection arguments
		properties = new Properties();
		properties.put("databaseName", dbName);
		if (integratedSecurity) {
			properties.put("integratedSecurity", true);
		} else {
			properties.put("username", username);
			properties.put("password", password);
		}
		// specify the driver to be used
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		// actualize connection
		connection = DriverManager.getConnection(url, properties);
		return connection;
	}

	/**
	 * @param fromDate
	 * @param toDate
	 * @param projectId
	 * @param proList 
	 * @param employeeId
	 * @param status
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public Set<Activity> getApproveList(Date fromDate, Date toDate,
			int projectId, Set<Project> proList, int employeeId, String status) throws SQLException, ClassNotFoundException {
		Set<Activity> result = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String sql = null;
		int paramIndex = 0;
		Activity activity = null;
		Project project = null;
		Employee employee = null;
		StringBuilder builder = null;
		int size = 0;
		if (proList != null && !proList.isEmpty()) {
			try {
				connection = connect();
				// 2. create query
				// 2.1. create the query
				sql = "SELECT DISTINCT d.DailyReportID AS id"
									+ ", d.EmployeeID AS empId"
									+ ", d.ProjectID AS proId"
									+ ", d.Date AS date"
									+ ", d.Description AS description"
									+ ", d.Duration AS duration"
									+ ", d.Status AS status"
									+ ", p.ProjectName AS proName"
									+ ", e.EmployeeName AS empName "
						+ "FROM DailyReport AS d "
							+ "LEFT JOIN Project AS p ON d.ProjectID = p.ProjectID "
							+ "LEFT JOIN Employee AS e ON d.EmployeeID = e.EmployeeID";
				// if a parameter is valid (not null, zero, etc), add the
				// corresponding search constraint to the query
				paramIndex = 1;
				if (fromDate != null) {
					sql += " WHERE d.Date >= ?";
					paramIndex++;
				}
				if (toDate != null) {
					if (paramIndex > 1) {
						sql += " AND d.Date <= ?";
					} else {
						sql += " WHERE d.Date <= ?";
					}
					paramIndex++;
				}
				if (projectId > 0) {
					if (paramIndex > 1) {
						sql += " AND d.ProjectID = ?";
					} else {
						sql += " WHERE d.ProjectID = ?";
					}
					paramIndex++;
				} else {
					builder = new StringBuilder();
					size = proList.size();
					for (int i = 0; i < size; i++) {
						builder.append("?, ");
					}
					builder.delete(builder.lastIndexOf(", "), builder.length());
					if (paramIndex > 1) {
						sql += " AND d.ProjectID IN (" + builder.toString() + ")";
					} else {
						sql += " WHERE d.ProjectID IN (" + builder.toString() + ")";
					}
					paramIndex += size;
				}
				if (employeeId > 0) {
					if (paramIndex > 1) {
						sql += " AND d.EmployeeID = ?";
					} else {
						sql += " WHERE d.EmployeeID = ?";
					}
					paramIndex++;
				}
				if (!CheckUtil.isBlankOrNull(status)) {
					if (paramIndex > 1) {
						sql += " AND d.Status = ?";
					} else {
						sql += " WHERE d.Status = ?";
					}
					paramIndex++;
				}
				// 2.2. turn the query to a preparedStatement
				statement = connection.prepareStatement(sql);
				// 2.3. add parameters to the statement
				paramIndex = 1;
				if (fromDate != null) {
					statement.setDate(paramIndex, FormatUtil.dateToDate(fromDate));
					paramIndex++;
				}
				if (toDate != null) {
					statement.setDate(paramIndex, FormatUtil.dateToDate(toDate));
					paramIndex++;
				}
				if (projectId > 0) {
					statement.setInt(paramIndex, projectId);
					paramIndex++;
				} else {
					for (Project pro : proList) {
						statement.setInt(paramIndex, pro.getId());
						paramIndex++;
					}
				}
				if (employeeId > 0) {
					statement.setInt(paramIndex, employeeId);
					paramIndex++;
				}
				if (!CheckUtil.isBlankOrNull(status)) {
					statement.setString(paramIndex, status);
					paramIndex++;
				}
				
				// 3. execute query
				resultSet = statement.executeQuery();
				
				// 4. read result
				result = new HashSet<Activity>();
				while (resultSet.next()) {
					activity = new Activity();
					activity.setId(resultSet.getInt("id"));
					activity.setDate(resultSet.getDate("date"));
					activity.setDescription(resultSet.getString("description"));
					activity.setStatus(resultSet.getString("status"));
					project = new Project();
					project.setId(resultSet.getInt("proId"));
					project.setName(resultSet.getString("proName"));
					activity.setProject(project);
					employee = new Employee();
					employee.setId(resultSet.getInt("empId"));
					employee.setName(resultSet.getString("empName"));
					activity.setEmployee(employee);
					activity.setDuration(resultSet.getDouble("duration"));
					result.add(activity);
				}
			} finally {
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			}
		}
		return result;
	}
	
	public int updateStatus(int[] ids, String status) throws SQLException, ClassNotFoundException {
		int result = 0;
		Connection connection = null;
		String sql = null;
		int size = 0;
		StringBuilder inClause = null;
		PreparedStatement statement = null;
		int paramIndex = 0;
		if (ids != null && ids.length > 0) {
			try {
				connection = connect();
				// 2. create query
				// 2.1. create the query
				size = ids.length;
				inClause = new StringBuilder();
				for (int i = 0; i < size; i++) {
					inClause.append("?, ");
				}
				inClause.delete(inClause.lastIndexOf(", "),
						inClause.length());
				sql = "UPDATE \"DailyReport\" SET \"Status\" = ? WHERE \"DailyReportID\" IN ("
						+ inClause.toString() + ")";
				// 2.2. turn the query to a preparedStatement
				statement = connection.prepareStatement(sql);
				// 2.3. add parameters to the statement
				paramIndex = 1;
				statement.setString(paramIndex, status);
				paramIndex++;
				for (int i = 0; i < size; i++) {
					statement.setInt(paramIndex, ids[i]);
					paramIndex++;
				}

				// 3. execute query
				result = statement.executeUpdate();
			} finally {
				if (statement != null) {
					statement.close();
				}
				if (connection != null) {
					connection.close();
				}
			}
		}
		return result;
	}
}
